﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
namespace copy
{
    public class DBHelper
    {
        private string connectionString = string.Empty;
        /// <summary> 
        /// 构造函数 
        /// </summary> 
        /// <param name="dbPath">SQLite数据库文件路径</param> 
        public DBHelper(string dbPath)
        {
            this.connectionString = "Data Source=" + dbPath;
        }

        #region - notice -

        public List<copyNotice> SelectnoticeTable(int noticeListId)
        {
            string sqlString = "SELECT Id,noticeListId,notice,content FROM notice WHERE noticeListId=@noticelistid ORDER BY Id";
            SQLiteParameter[] parameters=new SQLiteParameter[]
            {
                new SQLiteParameter("@noticelistid",noticeListId), 
            };
            DataTable dt=ExecuteDataTable(sqlString, parameters);
            if (dt == null) return null;
            if (dt.Rows.Count > 0)
            {
                List<copyNotice> lists=new List<copyNotice>();
                int k = dt.Rows.Count;
                for (int i = 0; i < k; i++)
                {
                    copyNotice c=new copyNotice();
                    c.Id = Convert.ToInt32(dt.Rows[i]["Id"]);
                    c.NoticelistId = Convert.ToInt32(dt.Rows[i]["noticeListId"]);
                    c.Notice = dt.Rows[i]["notice"].ToString();
                    c.Text = dt.Rows[i]["content"].ToString();
                    lists.Add(c);
                }
                return lists;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 插入一个Notice并返回Notice的id
        /// </summary>
        /// <param name="cn"></param>
        /// <returns></returns>
        public int InsertnoticeTable(copyNotice cn)
        {
            string sqlString = "INSERT INTO notice(noticeListId,notice,content) VALUES(@noticeListId,@notice,@content)";
            SQLiteParameter[] parameters=new SQLiteParameter[]
            {
                new SQLiteParameter("@noticeListId",cn.NoticelistId.ToString()),
                new SQLiteParameter("@notice",cn.Notice),
                new SQLiteParameter("@content",cn.Text), 
            };
            if (ExecuteNonQuery(sqlString, parameters) == 1)
            {
                sqlString = "SELECT Id FROM notice WHERE 1=1 ORDER BY Id desc";
                DataTable dt = ExecuteDataTable(sqlString, null);
                return int.Parse(dt.Rows[0][0].ToString());
            }
            else
            {
                return -1;
            }
        }
        /// <summary>
        /// 修改notie
        /// </summary>
        /// <param name="cn"></param>
        /// <returns></returns>
        public int UpdatenoticeTable(copyNotice cn)
        {
            string sqlString =
                "UPDATE notice SET notice=@notice,content=@content WHERE Id=@id and noticeListId=@noticelistid";
            SQLiteParameter[] parameters=new SQLiteParameter[]
            {
                new SQLiteParameter("@notice",cn.Notice),
                new SQLiteParameter("@content",cn.Text),
                new SQLiteParameter("@id",cn.Id),
                new SQLiteParameter("@noticelistid",cn.NoticelistId), 
            };
            return ExecuteNonQuery(sqlString, parameters);
        }

        #endregion
        #region - OpTable -
        public List<OpTable> SelectOpTable()
        {
            string sqlString = "SELECT name,value from op";
            List<OpTable> ots=new List<OpTable>();
            DataTable dt = ExecuteDataTable(sqlString, null);
            if (dt.Rows.Count == 0) return null;
            else
            {
                foreach (DataRow dataRow in dt.Rows)
                {
                    OpTable n = new OpTable();
                    n.Name = dataRow[0].ToString();
                    n.Value = dataRow[1].ToString();
                    ots.Add(n);
                }
                return ots;
            }
        }
        //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
        public bool UpdateOpTable(string name, string value)
        {
            SQLiteParameter[] select_parameters=new SQLiteParameter[]
            {
                new SQLiteParameter("@name",name), 
            };
            DataTable dt = (DataTable) ExecuteScalar("SELECT value FROM op WHERE name=@name;", select_parameters);
            if (dt.Rows.Count != 0)
            {
                string sqlString = "UPDATE op SET value=@value where name=@name;";
                SQLiteParameter[] parameters = new SQLiteParameter[] {
                    new SQLiteParameter("@name",name)
                    ,
                    new SQLiteParameter("@value", value) };
                return ExecuteNonQuery(sqlString, parameters) != 0;
            }
            else
            {
                return false;
            }
        }

        public bool InsertOpTable(string name,string value)
        {
            SQLiteParameter[] select_parameters=new SQLiteParameter[]
            {
                new SQLiteParameter("@name",name), 
            };
            DataTable dt = (DataTable) ExecuteScalar("SELECT value FROM op WHERE name=@name", select_parameters);
            if (dt.Rows.Count == 0)
            {
                string sqlString = "INSERT INTO op(name,value) VALUES(@name,@value)";
                SQLiteParameter[] parameters = new SQLiteParameter[] {
                    new SQLiteParameter("@name",name)
                    ,
                    new SQLiteParameter("@value", value) };
                return ExecuteNonQuery(sqlString, parameters) != 0;
            }
            else
            {
                return false;
            }
        }
        
        #endregion

        #region - noticelistTable -
        public int InsertnoticelistTable(string name)
        {
            string sqlString = "INSERT INTO noticelist(name) VALUES(@name)";
            SQLiteParameter[] parameters = new SQLiteParameter[] {
                new SQLiteParameter("@name",name)
                 };
            if (ExecuteNonQuery(sqlString, parameters) == 1)
            {
                sqlString = "SELECT id FROM noticelist WHERE 1=1 ORDER BY id desc";
                DataTable dt=ExecuteDataTable(sqlString, null);
                return int.Parse(dt.Rows[0][0].ToString());
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// select all noticelist
        /// return List<NoticeList>
        /// </summary>
        /// <returns></returns>
        public List<NoticeList> SelectnoticelistTable()
        {
            List<NoticeList> noticeLists=new List<NoticeList>();
            string sqlString = "SELECT id,name from noticelist";
            DataTable dt=ExecuteDataTable(sqlString,null);
            if (dt.Rows.Count == 0) return null;
            else
            {
                foreach (DataRow dataRow in dt.Rows)
                {
                    NoticeList n=new NoticeList();
                    n.Id = Convert.ToInt32(dataRow[0]);
                    n.Name = dataRow[1].ToString();
                    noticeLists.Add(n);
                }
                return noticeLists;
            }
        }

        #endregion

        #region - Base -
        /// <summary> 
        /// 创建SQLite数据库文件 
        /// </summary> 
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param> 
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    //command.CommandText = "CREATE TABLE notie(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,notie text,maintext text)";
                    //command.ExecuteNonQuery();
                    string opTable = "CREATE TABLE op(Id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(50),value varchar(100))";
                    command.CommandText = opTable;
                    command.ExecuteNonQuery();
                    string noticeListTable = "CREATE TABLE noticeList(Id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(100))";
                    command.CommandText = noticeListTable;
                    command.ExecuteNonQuery();
                    string noticeTable = "CREATE TABLE notice(Id INTEGER PRIMARY KEY AUTOINCREMENT,noticeListId INTEGER REFERENCES noticeList(Id),notice varchar(100),content text)";
                    command.CommandText = noticeTable;
                    command.ExecuteNonQuery();
                    
                }
            }
        }
        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = sql;
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            return affectedRows;
        }
        /// <summary> 
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        /// <summary> 
        /// 执行一个查询语句，返回查询结果的第一行第一列 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        /// <summary> 
        /// 查询数据库中的所有数据类型信息 
        /// </summary> 
        /// <returns></returns> 
        public DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns) 
                //{ 
                //        Console.WriteLine(column.ColumnName); 
                //} 
                return data;
            }
        }
        #endregion

    }
}
